<!-- build time:Tue Feb 18 2020 17:19:27 GMT+0800 (China Standard Time) --><!DOCTYPE html><html lang="zh"><head><meta charset="utf-8"><meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"><meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=1,minimum-scale=1,user-scalable=no,minimal-ui"><meta name="renderer" content="webkit"><meta http-equiv="Cache-Control" content="no-transform"><meta http-equiv="Cache-Control" content="no-siteapp"><meta name="apple-mobile-web-app-capable" content="yes"><meta name="apple-mobile-web-app-status-bar-style" content="black"><meta name="format-detection" content="telephone=no,email=no,adress=no"><meta name="theme-color" content="#000000"><meta http-equiv="window-target" content="_top"><title>Spring Boot 分库分表入门 | calm&#39;s blog</title><meta name="description" content="1. 概述因为市面上已经非常不错的分库分表的资料，所以卡姆就不在尴尬的瞎哔哔一些内容。推荐阅读两个资料：ShardingSphere 是目前最好用的数据库中间件之一，很多时候，我们使用它来实现分库分表，或者读写分离。当然，它不仅仅能够提供上述两个功能，也能提供分布式事务、数据库治理。目前，国内使用比较多的分库分表的中间件，主要有：Apache ShardingSphereMycat个人比较推荐使用"><meta property="og:type" content="article"><meta property="og:title" content="Spring Boot 分库分表入门"><meta property="og:url" content="https://calm_java.gitee.io/blog/2020/02/17/labs-springboot2-sharding-datasource/index.html"><meta property="og:site_name" content="卡姆"><meta property="og:description" content="1. 概述因为市面上已经非常不错的分库分表的资料，所以卡姆就不在尴尬的瞎哔哔一些内容。推荐阅读两个资料：ShardingSphere 是目前最好用的数据库中间件之一，很多时候，我们使用它来实现分库分表，或者读写分离。当然，它不仅仅能够提供上述两个功能，也能提供分布式事务、数据库治理。目前，国内使用比较多的分库分表的中间件，主要有：Apache ShardingSphereMycat个人比较推荐使用"><meta property="og:locale" content="zh_CN"><meta property="article:published_time" content="2020-02-17T08:15:31.000Z"><meta property="article:modified_time" content="2020-02-17T09:59:19.660Z"><meta property="article:author" content="calm"><meta property="article:tag" content="分库分表"><meta name="twitter:card" content="summary"><link rel="canonical" href="https://calm_java.gitee.io/blog/2020/02/17/labs-springboot2-sharding-datasource/index.html"><link rel="alternate" href="/atom.xml" title="卡姆" type="application/atom+xml"><link rel="icon" href="/blog/images/avatar.jpg" type="image/x-icon"><link rel="stylesheet" href="/blog/css/style.css"><link rel="stylesheet" href="//cdn.jsdelivr.net/npm/gitalk@1.4.0/dist/gitalk.min.css"><meta name="generator" content="Hexo 4.2.0"></head><body class="main-center theme-green" itemscope itemtype="http://schema.org/WebPage"><header class="header" itemscope itemtype="http://schema.org/WPHeader"><div class="slimContent"><div class="navbar-header"><div class="profile-block text-center"><a id="avatar" href="https://github.com/yuyong725" target="_blank"><img class="img-circle img-rotate" src="/blog/images/avatar.jpg" width="200" height="200"></a><h2 id="name" class="hidden-xs hidden-sm">卡姆</h2><h3 id="title" class="hidden-xs hidden-sm hidden-md">a Java coder need calm</h3><small id="location" class="text-muted hidden-xs hidden-sm"><i class="icon icon-map-marker"></i> hangzhou, China</small></div><div class="search" id="search-form-wrap"><form class="search-form sidebar-form"><div class="input-group"><input type="text" class="search-form-input form-control" placeholder="搜索"> <span class="input-group-btn"><button type="submit" class="search-form-submit btn btn-flat" onclick="return!1"><i class="icon icon-search"></i></button></span></div></form><div class="ins-search"><div class="ins-search-mask"></div><div class="ins-search-container"><div class="ins-input-wrapper"><input type="text" class="ins-search-input" placeholder="想要查找什么..." x-webkit-speech> <button type="button" class="close ins-close ins-selectable" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button></div><div class="ins-section-wrapper"><div class="ins-section-container"></div></div></div></div></div><button class="navbar-toggle collapsed" type="button" data-toggle="collapse" data-target="#main-navbar" aria-controls="main-navbar" aria-expanded="false"><span class="sr-only">Toggle navigation</span> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span></button></div><nav id="main-navbar" class="collapse navbar-collapse" itemscope itemtype="http://schema.org/SiteNavigationElement" role="navigation"><ul class="nav navbar-nav main-nav"><li class="menu-item menu-item-home"><a href="/blog/."><i class="icon icon-home-fill"></i> <span class="menu-title">首页</span></a></li><li class="menu-item menu-item-archives"><a href="/blog/archives"><i class="icon icon-archives-fill"></i> <span class="menu-title">归档</span></a></li><li class="menu-item menu-item-categories"><a href="/blog/categories"><i class="icon icon-folder"></i> <span class="menu-title">分类</span></a></li><li class="menu-item menu-item-tags"><a href="/blog/tags"><i class="icon icon-tags"></i> <span class="menu-title">标签</span></a></li><li class="menu-item menu-item-repository"><a href="/blog/repository"><i class="icon icon-project"></i> <span class="menu-title">项目</span></a></li><li class="menu-item menu-item-books"><a href="/blog/books"><i class="icon icon-book-fill"></i> <span class="menu-title">书单</span></a></li><li class="menu-item menu-item-links"><a href="/blog/links"><i class="icon icon-friendship"></i> <span class="menu-title">友链</span></a></li><li class="menu-item menu-item-about"><a href="/blog/about"><i class="icon icon-cup-fill"></i> <span class="menu-title">关于</span></a></li></ul><ul class="social-links"><li><a href="https://github.com/yuyong725" target="_blank" title="Github" data-toggle="tooltip" data-placement="top"><i class="icon icon-github"></i></a></li></ul></nav></div></header><aside class="sidebar" itemscope itemtype="http://schema.org/WPSideBar"><div class="slimContent"><div class="widget"><h3 class="widget-title">公告</h3><div class="widget-body"><div id="board"><div class="content"><p>欢迎交流与分享经验!</p></div></div></div></div><div class="widget"><h3 class="widget-title">分类</h3><div class="widget-body"><ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/blog/categories/CI-DI/">CI/DI</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/git/">git</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/labs/">labs</a><span class="category-list-count">14</span></li></ul></div></div><div class="widget"><h3 class="widget-title">标签</h3><div class="widget-body"><ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/MyBatis/" rel="tag">MyBatis</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Oauth2/" rel="tag">Oauth2</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Spring-Security/" rel="tag">Spring Security</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/SpringMVC/" rel="tag">SpringMVC</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/docker/" rel="tag">docker</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/dubbo/" rel="tag">dubbo</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/git/" rel="tag">git</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/jdbctemplate/" rel="tag">jdbctemplate</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/jerkins/" rel="tag">jerkins</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/job/" rel="tag">job</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/jpa/" rel="tag">jpa</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/pdf/" rel="tag">pdf</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/redis/" rel="tag">redis</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8/" rel="tag">分库分表</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/%E5%A4%9A%E6%95%B0%E6%8D%AE%E6%BA%90/" rel="tag">多数据源</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%89%88%E6%9C%AC%E7%AE%A1%E7%90%86/" rel="tag">数据库版本管理</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0/" rel="tag">数据库连接池</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/%E7%BC%93%E5%AD%98/" rel="tag">缓存</a><span class="tag-list-count">2</span></li></ul></div></div><div class="widget"><h3 class="widget-title">标签云</h3><div class="widget-body tagcloud"><a href="/blog/tags/MyBatis/" style="font-size:13px">MyBatis</a> <a href="/blog/tags/Oauth2/" style="font-size:13px">Oauth2</a> <a href="/blog/tags/Spring-Security/" style="font-size:13px">Spring Security</a> <a href="/blog/tags/SpringMVC/" style="font-size:13px">SpringMVC</a> <a href="/blog/tags/docker/" style="font-size:13px">docker</a> <a href="/blog/tags/dubbo/" style="font-size:13px">dubbo</a> <a href="/blog/tags/git/" style="font-size:13px">git</a> <a href="/blog/tags/jdbctemplate/" style="font-size:13px">jdbctemplate</a> <a href="/blog/tags/jerkins/" style="font-size:13px">jerkins</a> <a href="/blog/tags/job/" style="font-size:14px">job</a> <a href="/blog/tags/jpa/" style="font-size:13px">jpa</a> <a href="/blog/tags/pdf/" style="font-size:13px">pdf</a> <a href="/blog/tags/redis/" style="font-size:13px">redis</a> <a href="/blog/tags/%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8/" style="font-size:13px">分库分表</a> <a href="/blog/tags/%E5%A4%9A%E6%95%B0%E6%8D%AE%E6%BA%90/" style="font-size:13px">多数据源</a> <a href="/blog/tags/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%89%88%E6%9C%AC%E7%AE%A1%E7%90%86/" style="font-size:13px">数据库版本管理</a> <a href="/blog/tags/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0/" style="font-size:13px">数据库连接池</a> <a href="/blog/tags/%E7%BC%93%E5%AD%98/" style="font-size:14px">缓存</a></div></div><div class="widget"><h3 class="widget-title">归档</h3><div class="widget-body"><ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2020/02/">二月 2020</a><span class="archive-list-count">5</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2020/01/">一月 2020</a><span class="archive-list-count">8</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2019/12/">十二月 2019</a><span class="archive-list-count">4</span></li></ul></div></div><div class="widget"><h3 class="widget-title">最新文章</h3><div class="widget-body"><ul class="recent-post-list list-unstyled no-thumbnail"><li><div class="item-inner"><p class="item-category"><a class="category-link" href="/blog/categories/labs/">labs</a></p><p class="item-title"><a href="/blog/2020/02/18/labs-springboot2-async-Job/" class="title">Spring Boot 异步任务入门</a></p><p class="item-date"><time datetime="2020-02-18T05:10:39.000Z" itemprop="datePublished">2020-02-18</time></p></div></li><li><div class="item-inner"><p class="item-category"><a class="category-link" href="/blog/categories/labs/">labs</a></p><p class="item-title"><a href="/blog/2020/02/18/labs-springboot2-job/" class="title">Spring Boot 定时任务入门</a></p><p class="item-date"><time datetime="2020-02-18T02:26:31.000Z" itemprop="datePublished">2020-02-18</time></p></div></li><li><div class="item-inner"><p class="item-category"><a class="category-link" href="/blog/categories/labs/">labs</a></p><p class="item-title"><a href="/blog/2020/02/17/labs-springboot2-database-version-control/" class="title">Spring Boot 数据库版本管理入门</a></p><p class="item-date"><time datetime="2020-02-17T12:05:54.000Z" itemprop="datePublished">2020-02-17</time></p></div></li><li><div class="item-inner"><p class="item-category"><a class="category-link" href="/blog/categories/labs/">labs</a></p><p class="item-title"><a href="/blog/2020/02/17/labs-springboot2-sharding-datasource/" class="title">Spring Boot 分库分表入门</a></p><p class="item-date"><time datetime="2020-02-17T08:15:31.000Z" itemprop="datePublished">2020-02-17</time></p></div></li><li><div class="item-inner"><p class="item-category"><a class="category-link" href="/blog/categories/labs/">labs</a></p><p class="item-title"><a href="/blog/2020/02/13/labs-springboot2-dynamic-datasource/" class="title">Spring Boot 多数据源（读写分离）入门</a></p><p class="item-date"><time datetime="2020-02-13T03:18:37.000Z" itemprop="datePublished">2020-02-13</time></p></div></li></ul></div></div></div></aside><aside class="sidebar sidebar-toc collapse in" id="collapseToc" itemscope itemtype="http://schema.org/WPSideBar"><div class="slimContent"><nav id="toc" class="article-toc"><h3 class="toc-title">文章目录</h3><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#1-概述"><span class="toc-text">1. 概述</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#2-分库分表"><span class="toc-text">2. 分库分表</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#2-1-引入依赖"><span class="toc-text">2.1 引入依赖</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-2-Application"><span class="toc-text">2.2 Application</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-3-应用配置文件"><span class="toc-text">2.3 应用配置文件</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-4-MyBatis-配置文件"><span class="toc-text">2.4 MyBatis 配置文件</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-5-实体类"><span class="toc-text">2.5 实体类</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#2-5-1-OrderDO"><span class="toc-text">2.5.1 OrderDO</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-5-2-OrderConfigDO"><span class="toc-text">2.5.2 OrderConfigDO</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-6-Mapper"><span class="toc-text">2.6 Mapper</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#2-6-1-OrderMapper"><span class="toc-text">2.6.1 OrderMapper</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-6-2-OrderConfigMapper"><span class="toc-text">2.6.2 OrderConfigMapper</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-7-简单测试"><span class="toc-text">2.7 简单测试</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#2-7-1-OrderConfigMapperTest"><span class="toc-text">2.7.1 OrderConfigMapperTest</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-7-2-OrderMapperTest"><span class="toc-text">2.7.2 OrderMapperTest</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#3-读写分离"><span class="toc-text">3. 读写分离</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#3-1-引入依赖"><span class="toc-text">3.1 引入依赖</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-2-Application"><span class="toc-text">3.2 Application</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-3-应用配置文件-1"><span class="toc-text">2.3 应用配置文件</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-4-OrderDO"><span class="toc-text">2.4 OrderDO</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-5-OrderMapper"><span class="toc-text">2.5 OrderMapper</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-6-简单测试"><span class="toc-text">2.6 简单测试</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-7-详细测试"><span class="toc-text">2.7 详细测试</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#666-彩蛋"><span class="toc-text">666. 彩蛋</span></a></li></ol></nav></div></aside><main class="main" role="main"><div class="content"><article id="post-labs-springboot2-sharding-datasource" class="article article-type-post" itemscope itemtype="http://schema.org/BlogPosting"><div class="article-header"><h1 class="article-title" itemprop="name">Spring Boot 分库分表入门</h1><div class="article-meta"><span class="article-date"><i class="icon icon-calendar-check"></i> <a href="/blog/2020/02/17/labs-springboot2-sharding-datasource/" class="article-date"><time datetime="2020-02-17T08:15:31.000Z" itemprop="datePublished">2020-02-17</time></a></span> <span class="article-category"><i class="icon icon-folder"></i> <a class="article-category-link" href="/blog/categories/labs/">labs</a></span> <span class="article-tag"><i class="icon icon-tags"></i> <a class="article-tag-link" href="/blog/tags/%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8/" rel="tag">分库分表</a></span> <span class="article-read hidden-xs"><i class="icon icon-eye-fill" aria-hidden="true"></i> <span id="busuanzi_container_page_pv"><span id="busuanzi_value_page_pv">0</span></span></span> <span class="post-comment"><i class="icon icon-comment"></i> <a href="/blog/2020/02/17/labs-springboot2-sharding-datasource/#comments" class="article-comment-link">评论</a></span> <span class="post-wordcount hidden-xs" itemprop="wordCount">字数统计: 7.2k(字)</span> <span class="post-readcount hidden-xs" itemprop="timeRequired">阅读时长: 33(分)</span></div></div><div class="article-entry marked-body" itemprop="articleBody"><h1 id="1-概述"><a href="#1-概述" class="headerlink" title="1. 概述"></a>1. 概述</h1><p>因为市面上已经非常不错的分库分表的资料，所以卡姆就不在尴尬的瞎哔哔一些内容。推荐阅读两个资料：</p><ul><li>ShardingSphere 是目前最好用的数据库中间件之一，很多时候，我们使用它来实现分库分表，或者读写分离。当然，它不仅仅能够提供上述两个功能，也能提供分布式事务、数据库治理。</li></ul><p>目前，国内使用比较多的分库分表的中间件，主要有：</p><ul><li><a href="https://github.com/apache/incubator-shardingsphere" target="_blank" rel="noopener">Apache ShardingSphere</a></li><li><a href="https://github.com/MyCATApache/Mycat-Server" target="_blank" rel="noopener">Mycat</a></li></ul><p>个人比较推荐使用 ShardingSphere ，主要有几个原因：</p><ul><li><blockquote><p>关于 100+ 案例，并不是指的 100+ 公司采用，而是登记给 ShardingSphere 团队的公司数。实际肯定远超这个数字，毕竟大多数团队采用的话，都没去主动登记。</p></blockquote></li><li><p>社区强大，已经进入 Apache 孵化。并且有京东全职的开发团队，也有总共 88+ <a href="https://github.com/apache/incubator-shardingsphere/graphs/contributors" target="_blank" rel="noopener">contributors</a> 。</p></li><li><p>功能完善，不仅仅提供分库分表、读写分离，也提供分布式事务、数据库治理等功能。</p></li><li><p>之前学习 Sharding-JDBC 时，尝试写过一套<a href="http://www.iocoder.cn/categories/Sharding-JDBC/?self" target="_blank" rel="noopener">源码解析</a>文章。代码简直易读到爆炸。亮哥自己也在某次采访中，提到如下内容：以工匠精神去雕琢细节。 开放出去的源代码会在一定的范围内引起共鸣。一个值得研读开源项目，其代码必须经过雕琢，让其规范、一致、优雅、易懂，尽量将细节做到极致。通过代码质量给予使用者信心。</p></li><li><p>所以呢，非常推荐胖友尝试去阅读下 ShardingSphere 。</p></li></ul><p>可能会有胖友会提到 Mycat ，为什么不推荐使用它？默默不评价。如果在选型中考虑 Mycat 的话，推荐可以看看 <a href="https://github.com/actiontech/dble" target="_blank" rel="noopener">dble</a> 项目。</p><p>本文，我们会使用 ShardingSphere 的子项目 Sharding-JDBC 完成分库分表和读写分离的功能，会提供两个示例。如果胖友对 Sharding-JDBC 不是很了解，推荐先去阅读下 <a href="https://shardingsphere.apache.org/document/current/cn/overview/" target="_blank" rel="noopener">《Apache ShardingSphere 官方文档 —— 概览》</a> ，很简短。</p><h1 id="2-分库分表"><a href="#2-分库分表" class="headerlink" title="2. 分库分表"></a>2. 分库分表</h1><blockquote><p>示例代码对应仓库：<a href="https://github.com/yuyong725/labs-springboot2/tree/master/labs-springboot2-data-access/labs-springboot2-datasource-sharding/labs-springboot2-datasource-sharding-mybatis" target="_blank" rel="noopener">labs-springboot2-datasource-sharding-mybatis</a> 。</p></blockquote><p>本小节，我们会使用 Sharding-JDBC 实现分库分表的功能。我们会将 <code>orders</code> 订单表，拆分到 <strong>2</strong> 个库，每个库 <strong>4</strong> 个订单表，一共 <strong>8</strong> 个表。库表的情况如下：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">labs-springboot2-multiple 库</span><br><span class="line">  ├── sharding_orders_0</span><br><span class="line">  └── sharding_orders_2</span><br><span class="line">  └── sharding_orders_4</span><br><span class="line">  └── sharding_orders_6</span><br><span class="line">labs-springboot2 库</span><br><span class="line">  ├── sharding_orders_1</span><br><span class="line">  └── sharding_orders_3</span><br><span class="line">  └── sharding_orders_5</span><br><span class="line">  └── sharding_orders_7</span><br></pre></td></tr></table></figure><ul><li>偶数后缀的表，在 <code>labs-springboot2-multiple</code> 库下。</li><li>奇数后缀的表，在 <code>labs-springboot2</code> 库下。</li></ul><p>我们使用订单表上的 <code>user_id</code> 用户编号，进行分库分表的规则：</p><ul><li>首先，按照 <code>index = user_id % 2</code> 计算，将记录路由到 <code>ds-orders-${index}</code> 库。</li><li>然后，按照 <code>index = user_id % 8</code> 计算，将记录路由到 <code>sharding_orders_${index}</code> 表。</li></ul><p>举个例子：</p><table><thead><tr><th align="left">用户编号</th><th align="left">库</th><th align="left">表</th></tr></thead><tbody><tr><td align="left">1</td><td align="left"><code>labs-springboot2</code></td><td align="left"><code>sharding_orders_1</code></td></tr><tr><td align="left">2</td><td align="left"><code>labs-springboot2-multiple</code></td><td align="left"><code>sharding_orders_2</code></td></tr><tr><td align="left">3</td><td align="left"><code>labs-springboot2</code></td><td align="left"><code>sharding_orders_3</code></td></tr><tr><td align="left">4</td><td align="left"><code>labs-springboot2-multiple</code></td><td align="left"><code>sharding_orders_4</code></td></tr><tr><td align="left">5</td><td align="left"><code>labs-springboot2</code></td><td align="left"><code>sharding_orders_5</code></td></tr><tr><td align="left">6</td><td align="left"><code>labs-springboot2-multiple</code></td><td align="left"><code>sharding_orders_6</code></td></tr><tr><td align="left">7</td><td align="left"><code>labs-springboot2</code></td><td align="left"><code>sharding_orders_7</code></td></tr><tr><td align="left">8</td><td align="left"><code>labs-springboot2-multiple</code></td><td align="left"><code>sharding_orders_8</code></td></tr></tbody></table><p>考虑到部分表不需要分库分表，例如说 <code>order_config</code> 订单配置表，所以我们会配置路由到 <code>labs-springboot2</code> 库下。</p><p>具体 <code>orders</code> 和 <code>order_config</code> 两个表的创建语句，我们在 <a href="https://calm_java.gitee.io/blog/2020/02/17/labs-springboot2-sharding-datasource/">2.5 实体类</a> 提供。</p><p>因为本文重心在于提供示例。胖友可以碰到不理解的地方，看看如下文档：</p><ul><li><a href="https://shardingsphere.apache.org/document/current/cn/features/sharding/" target="_blank" rel="noopener">《ShardingSphere &gt; 概念 &amp; 功能 &gt; 数据分片》</a></li><li><a href="https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/usage/sharding/" target="_blank" rel="noopener">《ShardingSphere &gt; 用户手册 &gt; Sharding-JDBC &gt; 使用手册 &gt; 数据分片》</a></li><li><a href="https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/configuration/" target="_blank" rel="noopener">《ShardingSphere &gt; 用户手册 &gt; Sharding-JDBC &gt; 配置手册》</a></li></ul><h2 id="2-1-引入依赖"><a href="#2-1-引入依赖" class="headerlink" title="2.1 引入依赖"></a>2.1 引入依赖</h2><p>在 <code>pom.xml</code> 文件中，引入相关依赖。</p><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&lt;?xml version="1.0" encoding="UTF-8"?&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">project</span> <span class="attr">xmlns</span>=<span class="string">"http://maven.apache.org/POM/4.0.0"</span></span></span><br><span class="line"><span class="tag">         <span class="attr">xmlns:xsi</span>=<span class="string">"http://www.w3.org/2001/XMLSchema-instance"</span></span></span><br><span class="line"><span class="tag">         <span class="attr">xsi:schemaLocation</span>=<span class="string">"http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">parent</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>labs-springboot2-datasource-sharding<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>cn.javadog<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">version</span>&gt;</span>1.0-SNAPSHOT<span class="tag">&lt;/<span class="name">version</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">parent</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">modelVersion</span>&gt;</span>4.0.0<span class="tag">&lt;/<span class="name">modelVersion</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>labs-springboot2-datasource-sharding-mybatis<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">dependencies</span>&gt;</span></span><br><span class="line">        <span class="comment">&lt;!-- 实现对数据库连接池的自动化配置 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.springframework.boot<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>spring-boot-starter-jdbc<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line">        <span class="comment">&lt;!-- 本示例，我们使用 MySQL --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>mysql<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>mysql-connector-java<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line"></span><br><span class="line">        <span class="comment">&lt;!-- 实现对 MyBatis 的自动化配置 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.mybatis.spring.boot<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>mybatis-spring-boot-starter<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line"></span><br><span class="line">        <span class="comment">&lt;!-- 实现对 Sharding-JDBC 的自动化配置 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.apache.shardingsphere<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>sharding-jdbc-spring-boot-starter<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line"></span><br><span class="line">        <span class="comment">&lt;!-- 保证 Spring AOP 相关的依赖包 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.springframework<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>spring-aspects<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line"></span><br><span class="line">        <span class="comment">&lt;!-- 方便等会写单元测试 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.springframework.boot<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>spring-boot-starter-test<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">scope</span>&gt;</span>test<span class="tag">&lt;/<span class="name">scope</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">dependencies</span>&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="tag">&lt;/<span class="name">project</span>&gt;</span></span><br></pre></td></tr></table></figure><p>具体每个依赖的作用，胖友自己认真看下添加的所有注释噢。</p><h2 id="2-2-Application"><a href="#2-2-Application" class="headerlink" title="2.2 Application"></a>2.2 Application</h2><p>创建 <code>Application.java</code> 类，代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// Application.java</span></span><br><span class="line"></span><br><span class="line"><span class="meta">@SpringBootApplication</span></span><br><span class="line"><span class="meta">@MapperScan</span>(basePackages = <span class="string">"cn.javadog.springboot2.datasource.sharding.mybatis.mapper"</span>)</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">Application</span> </span>&#123;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><ul><li>添加 <code>@MapperScan</code> 注解，<code>cn.javadog.springboot2.datasource.sharding.mybatis.mapper</code> 包路径下，就是我们 Mapper 接口所在的包路径。</li></ul><h2 id="2-3-应用配置文件"><a href="#2-3-应用配置文件" class="headerlink" title="2.3 应用配置文件"></a>2.3 应用配置文件</h2><p>在 <code>resources</code> 目录下，创建 <code>application.yaml</code> 配置文件。配置如下：</p><figure class="highlight yaml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br></pre></td><td class="code"><pre><span class="line"><span class="attr">spring:</span></span><br><span class="line">  <span class="comment"># ShardingSphere 配置项</span></span><br><span class="line">  <span class="attr">shardingsphere:</span></span><br><span class="line">    <span class="attr">datasource:</span></span><br><span class="line">      <span class="comment"># 所有数据源的名字</span></span><br><span class="line">      <span class="attr">names:</span> <span class="string">ds-orders-0,</span> <span class="string">ds-orders-1</span></span><br><span class="line">      <span class="comment"># 订单 orders 数据源配置 00</span></span><br><span class="line">      <span class="attr">ds-orders-0:</span></span><br><span class="line">        <span class="attr">type:</span> <span class="string">com.zaxxer.hikari.HikariDataSource</span> <span class="comment"># 使用 Hikari 数据库连接池</span></span><br><span class="line">        <span class="attr">driver-class-name:</span> <span class="string">com.mysql.cj.jdbc.Driver</span></span><br><span class="line">        <span class="attr">jdbc-url:</span> <span class="string">jdbc:mysql://127.0.0.1:3306/labs-springboot2-multiple?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai</span></span><br><span class="line">        <span class="attr">username:</span> <span class="string">root</span></span><br><span class="line">        <span class="attr">password:</span> <span class="string">root</span></span><br><span class="line">      <span class="comment"># 订单 orders 数据源配置 01</span></span><br><span class="line">      <span class="attr">ds-orders-1:</span></span><br><span class="line">        <span class="attr">type:</span> <span class="string">com.zaxxer.hikari.HikariDataSource</span> <span class="comment"># 使用 Hikari 数据库连接池</span></span><br><span class="line">        <span class="attr">driver-class-name:</span> <span class="string">com.mysql.cj.jdbc.Driver</span></span><br><span class="line">        <span class="attr">jdbc-url:</span> <span class="string">jdbc:mysql://127.0.0.1:3306/labs-springboot2?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai</span></span><br><span class="line">        <span class="attr">username:</span> <span class="string">root</span></span><br><span class="line">        <span class="attr">password:</span> <span class="string">root</span></span><br><span class="line">    <span class="comment"># 分片规则</span></span><br><span class="line">    <span class="attr">sharding:</span></span><br><span class="line">      <span class="attr">tables:</span></span><br><span class="line">        <span class="comment"># orders 表配置</span></span><br><span class="line">        <span class="attr">sharding_orders:</span></span><br><span class="line"><span class="comment">#          actualDataNodes: ds-orders-$-&gt;&#123;0..1&#125;.orders_$-&gt;&#123;0..4&#125; # 映射到 ds-orders 数据源的 orders 表</span></span><br><span class="line"><span class="comment">#          actualDataNodes: ds-orders-0.orders_0, ds-orders-0.orders_2, ds-orders-0.orders_4, ds-orders-0.orders_6, ds-orders-1.orders_1, ds-orders-1.orders_3, ds-orders-1.orders_5, ds-orders-1.orders_7</span></span><br><span class="line">          <span class="attr">actualDataNodes:</span> <span class="string">ds-orders-0.sharding_orders_$-&gt;&#123;[0,2,4,6]&#125;,</span> <span class="string">ds-orders-1.sharding_orders_$-&gt;&#123;[1,3,5,7]&#125;</span> <span class="comment"># 映射到 ds-orders-0 和 ds-orders-1 数据源的 orders 表们</span></span><br><span class="line">          <span class="attr">key-generator:</span> <span class="comment"># 主键生成策略</span></span><br><span class="line">            <span class="attr">column:</span> <span class="string">id</span></span><br><span class="line">            <span class="attr">type:</span> <span class="string">SNOWFLAKE</span></span><br><span class="line">          <span class="attr">database-strategy:</span></span><br><span class="line">            <span class="attr">inline:</span></span><br><span class="line">              <span class="attr">algorithm-expression:</span> <span class="string">ds-orders-$-&gt;&#123;user_id</span> <span class="string">%</span> <span class="number">2</span><span class="string">&#125;</span></span><br><span class="line">              <span class="attr">sharding-column:</span> <span class="string">user_id</span></span><br><span class="line">          <span class="attr">table-strategy:</span></span><br><span class="line">            <span class="attr">inline:</span></span><br><span class="line">              <span class="attr">algorithm-expression:</span> <span class="string">sharding_orders_$-&gt;&#123;user_id</span> <span class="string">%</span> <span class="number">8</span><span class="string">&#125;</span></span><br><span class="line">              <span class="attr">sharding-column:</span> <span class="string">user_id</span></span><br><span class="line">        <span class="comment"># order_config 表配置</span></span><br><span class="line">        <span class="attr">sharding_order_config:</span></span><br><span class="line">          <span class="attr">actualDataNodes:</span> <span class="string">ds-orders-0.sharding_order_config</span> <span class="comment"># 仅映射到 ds-orders-0 数据源的 order_config 表</span></span><br><span class="line">    <span class="comment"># 拓展属性配置</span></span><br><span class="line">    <span class="attr">props:</span></span><br><span class="line">      <span class="attr">sql:</span></span><br><span class="line">        <span class="attr">show:</span> <span class="literal">true</span> <span class="comment"># 打印 SQL</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># mybatis 配置内容</span></span><br><span class="line"><span class="attr">mybatis:</span></span><br><span class="line">  <span class="attr">config-location:</span> <span class="string">classpath:mybatis-config.xml</span> <span class="comment"># 配置 MyBatis 配置文件路径</span></span><br><span class="line">  <span class="attr">mapper-locations:</span> <span class="string">classpath:mapper/*.xml</span> <span class="comment"># 配置 Mapper XML 地址</span></span><br><span class="line">  <span class="attr">type-aliases-package:</span> <span class="string">cn.javadog.springboot2.datasource.sharding.mybatis.dataobject</span> <span class="comment"># 配置数据库实体包路径</span></span><br></pre></td></tr></table></figure><ul><li>在 <code>mybatis</code> 配置项下，设置 <code>mybatis-spring-boot-starter</code> 自动化配置 MyBatis 需要的参数。</li><li>在 <code>spring.shardingsphere</code> 配置项下，设置 <code>sharding-jdbc-spring-boot-starter</code> 自动化配置 Sharding-JDBC 需要的参数。比较复杂，我们一个一个来看。</li></ul><p><code>spring.shardingsphere.datasource</code> 配置项，我们配置了 <code>ds-orders-0</code> 和 <code>ds-orders-1</code> 两个数据源，分别对应 <code>labs-springboot2-multiple</code> 和 <code>labs-springboot2</code> 两个数据库。</p><blockquote><p><strong>逻辑表</strong> ：水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为 10 张表，分别是 <code>t_order_0</code> 到 <code>t_order_9</code> ，他们的逻辑表名为 <code>t_order</code> 。</p><p><strong>真实表</strong> ：在分片的数据库中真实存在的物理表。即上个示例中的 <code>t_order_0</code> 到 <code>t_order_9</code> 。</p><p><strong>数据节点</strong> ：数据分片的最小单元。由数据源名称和数据表组成，例：<code>ds_0.t_order_0</code> 。</p></blockquote><ul><li><code>orders</code> 配置项，设置 <code>orders</code> 逻辑表，使用分库分表的规则。<ul><li><code>actualDataNodes</code> ：对应的数据节点，使用的是<a href="https://shardingsphere.apache.org/document/current/cn/features/sharding/other-features/inline-expression/" target="_blank" rel="noopener">行表达式</a> 。这里的意思是，<code>ds-orders-0.orders_0</code>, <code>ds-orders-0.orders_2</code>, <code>ds-orders-0.orders_4</code>, <code>ds-orders-0.orders_6</code>, <code>ds-orders-1.orders_1</code>, <code>ds-orders-1.orders_3</code>, <code>ds-orders-1.orders_5</code>, <code>ds-orders-1.orders_7</code> 。</li><li><code>key-generator</code> ：主键生成策略。这里采用分布式主键 SNOWFLAKE 方案。更多可以看 <a href="https://shardingsphere.apache.org/document/current/cn/features/sharding/other-features/key-generator/" target="_blank" rel="noopener">《 ShardingSphere &gt; 概念 &amp; 功能 &gt; 数据分片 &gt; 其他功能 &gt; 分布式主键》</a> 文档。</li><li><code>database-strategy</code> ：按照 <code>index = user_id % 2</code> 分库，路由到 <code>ds-orders-${index}</code> 数据源（库）。</li><li><code>table-strategy</code> ：<code>index = user_id % 8</code> 分表，路由到 <code>sharding_orders_${index}</code> 数据表。</li></ul></li><li><code>order_config</code> 配置项，设置 <code>order_config</code> 逻辑表，不使用分库分表。<ul><li><code>actualDataNodes</code> ：对应的数据节点，只对应数据源（库）为 <code>ds-orders-0</code> 的 <code>order_config</code> 表。</li></ul></li></ul><p><code>spring.shardingsphere.props</code> 配置项，设置拓展属性配置。</p><ul><li><code>sql.show</code> ：设置打印 SQL 。因为我们编写的 SQL 会被 Sharding-JDBC 进行处理，实际执行的可能不是我们编写的，通过打印，方便我们观察和理解。</li></ul><h2 id="2-4-MyBatis-配置文件"><a href="#2-4-MyBatis-配置文件" class="headerlink" title="2.4 MyBatis 配置文件"></a>2.4 MyBatis 配置文件</h2><p>在 <code>resources</code> 目录下，创建 <code>mybatis-config.xml</code> 配置文件。配置如下：</p><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&lt;?xml version="1.0" encoding="UTF-8" ?&gt;</span></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="meta-keyword">configuration</span> <span class="meta-keyword">PUBLIC</span> <span class="meta-string">"-//mybatis.org//DTD Config 3.0//EN"</span> <span class="meta-string">"http://mybatis.org/dtd/mybatis-3-config.dtd"</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">configuration</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">settings</span>&gt;</span></span><br><span class="line">        <span class="comment">&lt;!-- 使用驼峰命名法转换字段。 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">setting</span> <span class="attr">name</span>=<span class="string">"mapUnderscoreToCamelCase"</span> <span class="attr">value</span>=<span class="string">"true"</span>/&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">settings</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">typeAliases</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">typeAlias</span> <span class="attr">alias</span>=<span class="string">"Integer"</span> <span class="attr">type</span>=<span class="string">"java.lang.Integer"</span>/&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">typeAlias</span> <span class="attr">alias</span>=<span class="string">"Long"</span> <span class="attr">type</span>=<span class="string">"java.lang.Long"</span>/&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">typeAlias</span> <span class="attr">alias</span>=<span class="string">"HashMap"</span> <span class="attr">type</span>=<span class="string">"java.util.HashMap"</span>/&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">typeAlias</span> <span class="attr">alias</span>=<span class="string">"LinkedHashMap"</span> <span class="attr">type</span>=<span class="string">"java.util.LinkedHashMap"</span>/&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">typeAlias</span> <span class="attr">alias</span>=<span class="string">"ArrayList"</span> <span class="attr">type</span>=<span class="string">"java.util.ArrayList"</span>/&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">typeAlias</span> <span class="attr">alias</span>=<span class="string">"LinkedList"</span> <span class="attr">type</span>=<span class="string">"java.util.LinkedList"</span>/&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">typeAliases</span>&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="tag">&lt;/<span class="name">configuration</span>&gt;</span></span><br></pre></td></tr></table></figure><p>因为在数据库中的表的字段，我们是使用下划线风格，而数据库实体的字段使用驼峰风格，所以通过 <code>mapUnderscoreToCamelCase = true</code> 来自动转换。</p><h2 id="2-5-实体类"><a href="#2-5-实体类" class="headerlink" title="2.5 实体类"></a>2.5 实体类</h2><p>在 <code>cn.javadog.springboot2.datasource.sharding.mybatis.dataobject</code>包路径下，创建本小节的实体。</p><h3 id="2-5-1-OrderDO"><a href="#2-5-1-OrderDO" class="headerlink" title="2.5.1 OrderDO"></a>2.5.1 OrderDO</h3><p>创建 <code>OrderDO.java</code> 类。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderDO.java</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment"> * 订单 DO</span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"><span class="meta">@Data</span></span><br><span class="line"><span class="meta">@Accessors</span>(chain = <span class="keyword">true</span>)</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">OrderDO</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 订单编号</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="keyword">private</span> Long id;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 用户编号</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="keyword">private</span> Integer userId;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><p>在 <code>labs-springboot2-multiple</code> 数据库下，创建 <code>sharding_orders_0</code>、<code>sharding_orders_2</code>、<code>sharding_orders_4</code>、<code>sharding_orders_6</code> 、<code>sharding_order_config</code> 数据表。SQL 如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SET</span> <span class="keyword">NAMES</span> utf8mb4;</span><br><span class="line"><span class="keyword">SET</span> FOREIGN_KEY_CHECKS = <span class="number">0</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">-- Table structure for orders_0</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`sharding_orders_0`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`sharding_orders_0`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">bigint</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'订单编号'</span>,</span><br><span class="line">  <span class="string">`user_id`</span> <span class="built_in">int</span>(<span class="number">16</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">'用户编号'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_bin <span class="keyword">COMMENT</span>=<span class="string">'订单表'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">-- Table structure for orders_2</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`sharding_orders_2`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`sharding_orders_2`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">bigint</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'订单编号'</span>,</span><br><span class="line">  <span class="string">`user_id`</span> <span class="built_in">int</span>(<span class="number">16</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">'用户编号'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_bin <span class="keyword">COMMENT</span>=<span class="string">'订单表'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">-- Table structure for orders_4</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`sharding_orders_4`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`sharding_orders_4`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">bigint</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'订单编号'</span>,</span><br><span class="line">  <span class="string">`user_id`</span> <span class="built_in">int</span>(<span class="number">16</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">'用户编号'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_bin <span class="keyword">COMMENT</span>=<span class="string">'订单表'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">-- Table structure for orders_6</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`sharding_orders_6`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`sharding_orders_6`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">bigint</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'订单编号'</span>,</span><br><span class="line">  <span class="string">`user_id`</span> <span class="built_in">int</span>(<span class="number">16</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">'用户编号'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_bin <span class="keyword">COMMENT</span>=<span class="string">'订单表'</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">SET</span> FOREIGN_KEY_CHECKS = <span class="number">1</span>;</span><br></pre></td></tr></table></figure><p>在 <code>labs-springboot2</code> 数据库下，创建 <code>sharding_orders_1</code>、<code>sharding_orders_3</code>、<code>sharding_orders_5</code>、<code>sharding_orders_7</code> 数据表。SQL 如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SET</span> <span class="keyword">NAMES</span> utf8mb4;</span><br><span class="line"><span class="keyword">SET</span> FOREIGN_KEY_CHECKS = <span class="number">0</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">-- Table structure for orders_1</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`sharding_orders_1`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`sharding_orders_1`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">bigint</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'订单编号'</span>,</span><br><span class="line">  <span class="string">`user_id`</span> <span class="built_in">int</span>(<span class="number">16</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">'用户编号'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_bin <span class="keyword">COMMENT</span>=<span class="string">'订单表'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">-- Table structure for orders_3</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`sharding_orders_3`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`sharding_orders_3`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">bigint</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'订单编号'</span>,</span><br><span class="line">  <span class="string">`user_id`</span> <span class="built_in">int</span>(<span class="number">16</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">'用户编号'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_bin <span class="keyword">COMMENT</span>=<span class="string">'订单表'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">-- Table structure for orders_5</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`sharding_orders_5`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`sharding_orders_5`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">bigint</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'订单编号'</span>,</span><br><span class="line">  <span class="string">`user_id`</span> <span class="built_in">int</span>(<span class="number">16</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">'用户编号'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_bin <span class="keyword">COMMENT</span>=<span class="string">'订单表'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">-- Table structure for orders_7</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`sharding_orders_7`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`sharding_orders_7`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">bigint</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'订单编号'</span>,</span><br><span class="line">  <span class="string">`user_id`</span> <span class="built_in">int</span>(<span class="number">16</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">'用户编号'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_bin <span class="keyword">COMMENT</span>=<span class="string">'订单表'</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SET</span> FOREIGN_KEY_CHECKS = <span class="number">1</span>;</span><br></pre></td></tr></table></figure><h3 id="2-5-2-OrderConfigDO"><a href="#2-5-2-OrderConfigDO" class="headerlink" title="2.5.2 OrderConfigDO"></a>2.5.2 OrderConfigDO</h3><p>创建 <code>OrderConfigDO.java</code>类。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderConfigDO.java</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/**</span></span><br><span class="line"><span class="comment"> * 订单配置 DO</span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"><span class="meta">@Data</span></span><br><span class="line"><span class="meta">@Accessors</span>(chain = <span class="keyword">true</span>)</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">OrderConfigDO</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 编号</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="keyword">private</span> Integer id;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 支付超时时间</span></span><br><span class="line"><span class="comment">     *</span></span><br><span class="line"><span class="comment">     * 单位：分钟</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="keyword">private</span> Integer payTimeout;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><p>在 <code>labs-springboot2-multiple</code> 数据库下，创建 <code>order_config</code> 数据表。SQL 如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="comment">-- Table structure for order_config</span></span><br><span class="line"><span class="comment">-- ----------------------------</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`sharding_order_config`</span>;</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`sharding_order_config`</span> (</span><br><span class="line">  <span class="string">`id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT <span class="keyword">COMMENT</span> <span class="string">'编号'</span>,</span><br><span class="line">  <span class="string">`pay_timeout`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> <span class="keyword">COMMENT</span> <span class="string">'支付超时时间;单位：分钟'</span>,</span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8mb4 <span class="keyword">COLLATE</span>=utf8mb4_bin;</span><br></pre></td></tr></table></figure><h2 id="2-6-Mapper"><a href="#2-6-Mapper" class="headerlink" title="2.6 Mapper"></a>2.6 Mapper</h2><p>在 <code>cn.javadog.springboot2.datasource.sharding.mybatis.mapper</code> 包路径下，创建相应的 Mapper 接口。</p><h3 id="2-6-1-OrderMapper"><a href="#2-6-1-OrderMapper" class="headerlink" title="2.6.1 OrderMapper"></a>2.6.1 OrderMapper</h3><p>创建 <code>OrderMapper.java</code> 类。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderMapper.java</span></span><br><span class="line"></span><br><span class="line"><span class="meta">@Repository</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">interface</span> <span class="title">OrderMapper</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="function">OrderDO <span class="title">selectById</span><span class="params">(@Param(<span class="string">"id"</span>)</span> Integer id)</span>;</span><br><span class="line"></span><br><span class="line">    <span class="function">List&lt;OrderDO&gt; <span class="title">selectListByUserId</span><span class="params">(@Param(<span class="string">"userId"</span>)</span> Integer userId)</span>;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">void</span> <span class="title">insert</span><span class="params">(OrderDO order)</span></span>;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><p>在 <code>resources/mapper</code> 路径下，创建 <code>OrderMapper.xml</code> 接口。代码如下：</p><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&lt;?xml version="1.0" encoding="UTF-8"?&gt;</span></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="meta-keyword">mapper</span> <span class="meta-keyword">PUBLIC</span> <span class="meta-string">"-//mybatis.org//DTD Mapper 3.0//EN"</span> <span class="meta-string">"http://mybatis.org/dtd/mybatis-3-mapper.dtd"</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">mapper</span> <span class="attr">namespace</span>=<span class="string">"cn.javadog.springboot2.datasource.sharding.mybatis.mapper.OrderMapper"</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">sql</span> <span class="attr">id</span>=<span class="string">"FIELDS"</span>&gt;</span></span><br><span class="line">        id, user_id</span><br><span class="line">    <span class="tag">&lt;/<span class="name">sql</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"selectById"</span> <span class="attr">parameterType</span>=<span class="string">"Integer"</span> <span class="attr">resultType</span>=<span class="string">"OrderDO"</span>&gt;</span></span><br><span class="line">        SELECT</span><br><span class="line">            <span class="tag">&lt;<span class="name">include</span> <span class="attr">refid</span>=<span class="string">"FIELDS"</span> /&gt;</span></span><br><span class="line">        FROM sharding_orders</span><br><span class="line">        WHERE id = #&#123;id&#125;</span><br><span class="line">    <span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"selectListByUserId"</span> <span class="attr">parameterType</span>=<span class="string">"Integer"</span> <span class="attr">resultType</span>=<span class="string">"OrderDO"</span>&gt;</span></span><br><span class="line">        SELECT</span><br><span class="line">            <span class="tag">&lt;<span class="name">include</span> <span class="attr">refid</span>=<span class="string">"FIELDS"</span> /&gt;</span></span><br><span class="line">        FROM sharding_orders</span><br><span class="line">        WHERE user_id = #&#123;userId&#125;</span><br><span class="line">    <span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">insert</span> <span class="attr">id</span>=<span class="string">"insert"</span> <span class="attr">parameterType</span>=<span class="string">"OrderDO"</span> <span class="attr">useGeneratedKeys</span>=<span class="string">"true"</span> <span class="attr">keyProperty</span>=<span class="string">"id"</span>&gt;</span></span><br><span class="line">        INSERT INTO sharding_orders (</span><br><span class="line">            user_id</span><br><span class="line">        ) VALUES (</span><br><span class="line">            #&#123;userId&#125;</span><br><span class="line">        )</span><br><span class="line">    <span class="tag">&lt;/<span class="name">insert</span>&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="tag">&lt;/<span class="name">mapper</span>&gt;</span></span><br></pre></td></tr></table></figure><h3 id="2-6-2-OrderConfigMapper"><a href="#2-6-2-OrderConfigMapper" class="headerlink" title="2.6.2 OrderConfigMapper"></a>2.6.2 OrderConfigMapper</h3><p>创建 <code>OrderConfigMapper.java</code> 类。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderConfigMapper.java</span></span><br><span class="line"></span><br><span class="line"><span class="meta">@Repository</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">interface</span> <span class="title">OrderConfigMapper</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="function">OrderConfigDO <span class="title">selectById</span><span class="params">(@Param(<span class="string">"id"</span>)</span> Integer id)</span>;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><p>在 <code>resources/mapper</code> 路径下，创建 <code>OrderConfigMapper.xml</code> 接口。代码如下：</p><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&lt;?xml version="1.0" encoding="UTF-8"?&gt;</span></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="meta-keyword">mapper</span> <span class="meta-keyword">PUBLIC</span> <span class="meta-string">"-//mybatis.org//DTD Mapper 3.0//EN"</span> <span class="meta-string">"http://mybatis.org/dtd/mybatis-3-mapper.dtd"</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">mapper</span> <span class="attr">namespace</span>=<span class="string">"cn.javadog.springboot2.datasource.sharding.mybatis.mapper.OrderConfigMapper"</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">sql</span> <span class="attr">id</span>=<span class="string">"FIELDS"</span>&gt;</span></span><br><span class="line">        id, pay_timeout</span><br><span class="line">    <span class="tag">&lt;/<span class="name">sql</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">select</span> <span class="attr">id</span>=<span class="string">"selectById"</span> <span class="attr">parameterType</span>=<span class="string">"Integer"</span> <span class="attr">resultType</span>=<span class="string">"OrderConfigDO"</span>&gt;</span></span><br><span class="line">        SELECT</span><br><span class="line">            <span class="tag">&lt;<span class="name">include</span> <span class="attr">refid</span>=<span class="string">"FIELDS"</span> /&gt;</span></span><br><span class="line">        FROM sharding_order_config</span><br><span class="line">        WHERE id = #&#123;id&#125;</span><br><span class="line">    <span class="tag">&lt;/<span class="name">select</span>&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="tag">&lt;/<span class="name">mapper</span>&gt;</span></span><br></pre></td></tr></table></figure><h2 id="2-7-简单测试"><a href="#2-7-简单测试" class="headerlink" title="2.7 简单测试"></a>2.7 简单测试</h2><h3 id="2-7-1-OrderConfigMapperTest"><a href="#2-7-1-OrderConfigMapperTest" class="headerlink" title="2.7.1 OrderConfigMapperTest"></a>2.7.1 OrderConfigMapperTest</h3><p>创建 <code>OrderConfigMapperTest</code> 测试类，我们来测试一下简单的 OrderConfigMapper 的每个操作。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderConfigMapperTest.java</span></span><br><span class="line"></span><br><span class="line"><span class="meta">@RunWith</span>(SpringRunner<span class="class">.<span class="keyword">class</span>)</span></span><br><span class="line"><span class="class">@<span class="title">SpringBootTest</span>(<span class="title">classes</span> </span>= Application<span class="class">.<span class="keyword">class</span>)</span></span><br><span class="line"><span class="class"><span class="title">public</span> <span class="title">class</span> <span class="title">OrderConfigMapperTest</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Autowired</span></span><br><span class="line">    <span class="keyword">private</span> OrderConfigMapper orderConfigMapper;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Test</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">testSelectById</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        OrderConfigDO orderConfig = orderConfigMapper.selectById(<span class="number">1</span>);</span><br><span class="line">        System.out.println(orderConfig);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><p><strong><code>#testSelectByI()</code> 测试方法</strong></p><p>执行日志如下：</p><figure class="highlight verilog"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// Logic SQL</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">16</span>:<span class="number">55</span>:<span class="number">57</span><span class="variable">.917</span>  INFO <span class="number">11371</span> --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT</span><br><span class="line"></span><br><span class="line">        id, pay_timeout</span><br><span class="line"></span><br><span class="line">        FROM order_config</span><br><span class="line">        WHERE id = ?</span><br><span class="line"></span><br><span class="line"><span class="comment">// Actual SQL</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">16</span>:<span class="number">55</span>:<span class="number">57</span><span class="variable">.918</span>  INFO <span class="number">11371</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">0</span> ::: SELECT</span><br><span class="line"></span><br><span class="line">        id, pay_timeout</span><br><span class="line"></span><br><span class="line">        FROM order_config</span><br><span class="line">        WHERE id = ? ::: [<span class="number">1</span>]</span><br></pre></td></tr></table></figure><ul><li>Logic SQL ：逻辑 SQL 日志，就是我们编写的。</li><li>Actual SQL ：物理 SQL 日志，实际 Sharding-JDBC 向数据库真正发起的日志。<ul><li>在这里，我们可以看到 <code>ds-orders-0</code> ，表名该物理 SQL ，是路由到 <code>ds-orders-0</code> 数据源执行。</li><li>同时，查询的是 <code>order_config</code> 表。</li><li>符合我们配置的 <code>order_config</code> 逻辑表，不使用分库分表，对应的数据节点仅有 <code>ds-orders-0.order_config</code> 。</li></ul></li></ul><h3 id="2-7-2-OrderMapperTest"><a href="#2-7-2-OrderMapperTest" class="headerlink" title="2.7.2 OrderMapperTest"></a>2.7.2 OrderMapperTest</h3><p>创建 <code>OrderMapperTest</code> 测试类，我们来测试一下简单的 OrderMapper 的每个操作。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderMapperTest.java</span></span><br><span class="line"></span><br><span class="line"><span class="meta">@RunWith</span>(SpringRunner<span class="class">.<span class="keyword">class</span>)</span></span><br><span class="line"><span class="class">@<span class="title">SpringBootTest</span>(<span class="title">classes</span> </span>= Application<span class="class">.<span class="keyword">class</span>)</span></span><br><span class="line"><span class="class"><span class="title">public</span> <span class="title">class</span> <span class="title">OrderMapperTest</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Autowired</span></span><br><span class="line">    <span class="keyword">private</span> OrderMapper orderMapper;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Test</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">testSelectById</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        OrderDO order = orderMapper.selectById(<span class="number">1</span>);</span><br><span class="line">        System.out.println(order);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Test</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">testSelectListByUserId</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        List&lt;OrderDO&gt; orders = orderMapper.selectListByUserId(<span class="number">1</span>);</span><br><span class="line">        System.out.println(orders.size());</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Test</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">testInsert</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        OrderDO order = <span class="keyword">new</span> OrderDO();</span><br><span class="line">        order.setUserId(<span class="number">1</span>);</span><br><span class="line">        orderMapper.insert(order);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><p><strong>① <code>#testSelectById()</code> 测试方法</strong></p><p>执行日志如下：</p><figure class="highlight verilog"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// Logic SQL</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">03</span>:<span class="number">00</span><span class="variable">.718</span>  INFO <span class="number">11379</span> --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders</span><br><span class="line">        WHERE id = ?</span><br><span class="line"></span><br><span class="line"><span class="comment">// Actual SQL</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">03</span>:<span class="number">00</span><span class="variable">.718</span>  INFO <span class="number">11379</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">0</span> ::: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders_0</span><br><span class="line">        WHERE id = ? ::: [<span class="number">1</span>]</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">03</span>:<span class="number">00</span><span class="variable">.718</span>  INFO <span class="number">11379</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">0</span> ::: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders_2</span><br><span class="line">        WHERE id = ? ::: [<span class="number">1</span>]</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">03</span>:<span class="number">00</span><span class="variable">.718</span>  INFO <span class="number">11379</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">0</span> ::: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders_4</span><br><span class="line">        WHERE id = ? ::: [<span class="number">1</span>]</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">03</span>:<span class="number">00</span><span class="variable">.718</span>  INFO <span class="number">11379</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">0</span> ::: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders_6</span><br><span class="line">        WHERE id = ? ::: [<span class="number">1</span>]</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">03</span>:<span class="number">00</span><span class="variable">.718</span>  INFO <span class="number">11379</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">1</span> ::: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders_1</span><br><span class="line">        WHERE id = ? ::: [<span class="number">1</span>]</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">03</span>:<span class="number">00</span><span class="variable">.718</span>  INFO <span class="number">11379</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">1</span> ::: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders_3</span><br><span class="line">        WHERE id = ? ::: [<span class="number">1</span>]</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">03</span>:<span class="number">00</span><span class="variable">.718</span>  INFO <span class="number">11379</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">1</span> ::: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders_5</span><br><span class="line">        WHERE id = ? ::: [<span class="number">1</span>]</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">03</span>:<span class="number">00</span><span class="variable">.719</span>  INFO <span class="number">11379</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">1</span> ::: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders_7</span><br><span class="line">        WHERE id = ? ::: [<span class="number">1</span>]</span><br></pre></td></tr></table></figure><ul><li>明明只有一条 Logic SQL 操作，却发起了 8 条 Actual SQL 操作。这是为什么呢？</li><li>通过将 Actual SQL 在每个数据库的数据表执行，返回的结果都是符合条件的。这样，和使用 Logic SQL 在逻辑表中执行的结果，实际是一致的。</li><li>那么，一次性发起这么多条 Actual SQL 是不是会顺序执行，导致很慢呢？实际上，Sharding-JDBC 有 <a href="https://shardingsphere.apache.org/document/current/cn/features/sharding/principle/execute/" target="_blank" rel="noopener">执行引擎</a> ，会并行执行这多条 Actual SQL 操作。所以呢，最终操作时长，由最慢的 Actual SQL 所决定。</li><li>虽然说，<a href="https://shardingsphere.apache.org/document/current/cn/features/sharding/principle/execute/" target="_blank" rel="noopener">执行引擎</a> 提供了并行执行 Actual SQL 操作的能力，我们还是推荐尽可能查询的时候，带有片键（分库分表字段）。对 Sharding-JDBC 性能感兴趣的胖友，可以看看 <a href="https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/stress-test/" target="_blank" rel="noopener">《Sharding-JDBC 性能测试报告》</a> 。</li></ul><p><strong>② <code>#testSelectListByUserId()</code> 测试方法</strong></p><p>执行日志如下：</p><figure class="highlight verilog"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// Logic SQL</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">21</span>:<span class="number">49</span><span class="variable">.861</span>  INFO <span class="number">11421</span> --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders</span><br><span class="line">        WHERE user_id = ?</span><br><span class="line"></span><br><span class="line"><span class="comment">// Actual SQL</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">21</span>:<span class="number">49</span><span class="variable">.861</span>  INFO <span class="number">11421</span> --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT</span><br><span class="line">             </span><br><span class="line">        id, user_id</span><br><span class="line">     </span><br><span class="line">        FROM sharding_orders</span><br><span class="line">        WHERE user_id = ?</span><br></pre></td></tr></table></figure><ul><li>一条 Logic SQL 操作，发起了 1 条 Actual SQL 操作。这是为什么呢？</li><li>我们使用<code>user_id = ?</code> 作为查询条件，因为 Sharding-JDBC 解析到我们配置的 <code>user_id</code>片键（分库分表字段），作为查询字段，所以可以 <a href="https://shardingsphere.apache.org/document/current/cn/features/sharding/principle/route/" target="_blank" rel="noopener">标准路由</a> ，仅查询一个 数据节点。这种，是 Sharding-JDBC 最为推荐使用的分片方式。<ul><li>分库：<code>user_id % 2 = 1 % 2 = 1</code> ，所以路由到 <code>ds-orders-1</code> 数据源。</li><li>分表：<code>user_id % 8 = 1 % 8 = 1</code> ，所以路由到 <code>sharding_orders_1</code> 数据表。</li><li>两者一结合，只查询 <code>ds-orders-1.sharding_orders_1</code> 数据节点。</li></ul></li></ul><p><strong>② <code>#testInsert()</code> 测试方法</strong></p><p>执行日志如下：</p><figure class="highlight verilog"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// Logic SQL</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">26</span>:<span class="number">40</span><span class="variable">.654</span>  INFO <span class="number">11430</span> --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO sharding_orders (</span><br><span class="line">            user_id</span><br><span class="line">        ) VALUES (</span><br><span class="line">            ?</span><br><span class="line">        )</span><br><span class="line"></span><br><span class="line"><span class="comment">// Actual SQL</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">26</span>:<span class="number">40</span><span class="variable">.655</span>  INFO <span class="number">11430</span> --- [           main] ShardingSphere-SQL                       : Actual SQL: ds-orders-<span class="number">1</span> ::: INSERT INTO sharding_orders_1 (</span><br><span class="line">            user_id</span><br><span class="line">        , id) VALUES (?, ?) ::: [<span class="number">1</span>, <span class="number">436216006948421633</span>]</span><br></pre></td></tr></table></figure><ul><li>不考虑 <a href="https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/sql/" target="_blank" rel="noopener">广播表</a> 的情况下，插入语句必须带有片键（分库分表字段），否则 <a href="https://shardingsphere.apache.org/document/current/cn/features/sharding/principle/execute/" target="_blank" rel="noopener">执行引擎</a> 不知道插入到哪个数据库的哪个数据表中。毕竟，插入操作必然是单库单表。</li><li>我们会发现，Actual SQL 相比 Logic SQL 来说，增加了主键 <code>id</code> 为 <code>436216006948421633</code> 。这是为什么呢？我们配置 <code>orders</code> 逻辑表，使用 SNOWFLAKE 算法生成分布式主键，而 <a href="https://shardingsphere.apache.org/document/current/cn/features/sharding/principle/rewrite/" target="_blank" rel="noopener">改写引擎</a> 在发现我们的 Logic SQL 并未设置插入的 <code>id</code> 主键编号，它会自动生成主键，改写 Logic SQL ，附加 <code>id</code> 成 Logic SQL 。</li></ul><p>至此，我们已经完成了一个 Sharding-JDBC 的简单的分库分表的示例。卡姆建议的话，如果准备应用到项目之前，通读 <a href="https://shardingsphere.apache.org/document/current/cn/overview/" target="_blank" rel="noopener">《ShardingSphere 文档》</a> 。学习不全面，线上两行泪。</p><h1 id="3-读写分离"><a href="#3-读写分离" class="headerlink" title="3. 读写分离"></a>3. 读写分离</h1><p>在 <a href="https://calm_java.gitee.io/blog/2020/02/13/labs-springboot2-dynamic-datasource/">《 Spring Boot 多数据源（读写分离）入门》</a> 的 <a href="https://calm_java.gitee.io/blog/2020/02/13/labs-springboot2-dynamic-datasource/">「9. Sharding-JDBC 读写分离」</a> 小节中，我们已经提供了使用 Sharding-JDBC 实现读写分离的入门示例。</p><p>本小节，我们会使用 <a href="https://mp.baomidou.com/" target="_blank" rel="noopener">MyBatis-Plus</a> 替换掉原生 MyBatis ，进一步简化该示例。</p><ul><li>当然，即使你没看过上述示例，也不影响本小节的阅读与入门。</li><li>可能胖友没有使用过 MyBatis-Plus ，也请放心，一样不会有影响。</li></ul><h2 id="3-1-引入依赖"><a href="#3-1-引入依赖" class="headerlink" title="3.1 引入依赖"></a>3.1 引入依赖</h2><blockquote><p>示例代码对应仓库：<a href="https://github.com/yuyong725/labs-springboot2/tree/master/labs-springboot2-data-access/labs-springboot2-datasource-sharding/labs-springboot2-datasource-sharding-baomidou" target="_blank" rel="noopener">labs-springboot2-datasource-sharding-baomidou</a> 。</p></blockquote><p>在 <code>pom.xml</code> 文件中，引入相关依赖。</p><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&lt;?xml version="1.0" encoding="UTF-8"?&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">project</span> <span class="attr">xmlns</span>=<span class="string">"http://maven.apache.org/POM/4.0.0"</span></span></span><br><span class="line"><span class="tag">         <span class="attr">xmlns:xsi</span>=<span class="string">"http://www.w3.org/2001/XMLSchema-instance"</span></span></span><br><span class="line"><span class="tag">         <span class="attr">xsi:schemaLocation</span>=<span class="string">"http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">parent</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>labs-springboot2-datasource-sharding<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>cn.javadog<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">version</span>&gt;</span>1.0-SNAPSHOT<span class="tag">&lt;/<span class="name">version</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">parent</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">modelVersion</span>&gt;</span>4.0.0<span class="tag">&lt;/<span class="name">modelVersion</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>labs-springboot2-datasource-sharding-baomidou<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">dependencies</span>&gt;</span></span><br><span class="line">        <span class="comment">&lt;!-- 实现对数据库连接池的自动化配置 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.springframework.boot<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>spring-boot-starter-jdbc<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line">        <span class="comment">&lt;!-- 本示例，我们使用 MySQL --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>mysql<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>mysql-connector-java<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line"></span><br><span class="line">        <span class="comment">&lt;!-- 实现对 Sharding-JDBC 的自动化配置 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.apache.shardingsphere<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>sharding-jdbc-spring-boot-starter<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line"></span><br><span class="line">        <span class="comment">&lt;!-- 实现对 MyBatis Plus 的自动化配置 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>com.baomidou<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>mybatis-plus-boot-starter<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">version</span>&gt;</span>3.2.0<span class="tag">&lt;/<span class="name">version</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line"></span><br><span class="line">        <span class="comment">&lt;!-- 方便等会写单元测试 --&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>org.springframework.boot<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>spring-boot-starter-test<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">            <span class="tag">&lt;<span class="name">scope</span>&gt;</span>test<span class="tag">&lt;/<span class="name">scope</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">dependencies</span>&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="tag">&lt;/<span class="name">project</span>&gt;</span></span><br></pre></td></tr></table></figure><p>具体每个依赖的作用，胖友自己认真看下添加的所有注释噢。</p><h2 id="3-2-Application"><a href="#3-2-Application" class="headerlink" title="3.2 Application"></a>3.2 Application</h2><p>创建 <code>Application.java</code> 类，配置 <code>@MapperScan</code> 注解，扫描对应 Mapper 接口所在的包路径。代码如下：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">&#x2F;&#x2F; Application.java</span><br><span class="line"></span><br><span class="line">@SpringBootApplication</span><br><span class="line">@MapperScan(basePackages &#x3D; &quot;cn.javadog.springboot2.datasource.sharding.baomidou.mapper&quot;)</span><br><span class="line">public class Application &#123;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><ul><li><code>cn.javadog.springboot2.datasource.sharding.baomidou.mapper</code> 包路径下，就是我们 Mapper 接口所在的包路径。</li></ul><h2 id="2-3-应用配置文件-1"><a href="#2-3-应用配置文件-1" class="headerlink" title="2.3 应用配置文件"></a>2.3 应用配置文件</h2><p>在 <code>resources</code> 目录下，创建 <code>application.yaml</code> 配置文件。配置如下：</p><figure class="highlight yaml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br></pre></td><td class="code"><pre><span class="line"><span class="attr">spring:</span></span><br><span class="line">  <span class="comment"># ShardingSphere 配置项</span></span><br><span class="line">  <span class="attr">shardingsphere:</span></span><br><span class="line">    <span class="comment"># 数据源配置</span></span><br><span class="line">    <span class="attr">datasource:</span></span><br><span class="line">      <span class="comment"># 所有数据源的名字</span></span><br><span class="line">      <span class="attr">names:</span> <span class="string">ds-master,</span> <span class="string">ds-slave-1,</span> <span class="string">ds-slave-2</span></span><br><span class="line">      <span class="comment"># 订单 orders 主库的数据源配置</span></span><br><span class="line">      <span class="attr">ds-master:</span></span><br><span class="line">        <span class="attr">type:</span> <span class="string">com.zaxxer.hikari.HikariDataSource</span> <span class="comment"># 使用 Hikari 数据库连接池</span></span><br><span class="line">        <span class="attr">driver-class-name:</span> <span class="string">com.mysql.cj.jdbc.Driver</span></span><br><span class="line">        <span class="attr">jdbc-url:</span> <span class="string">jdbc:mysql://127.0.0.1:3306/labs-springboot2?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai</span></span><br><span class="line">        <span class="attr">username:</span> <span class="string">root</span></span><br><span class="line">        <span class="attr">password:</span> <span class="string">root</span></span><br><span class="line">      <span class="comment"># 订单 orders 从库数据源配置</span></span><br><span class="line">      <span class="attr">ds-slave-1:</span></span><br><span class="line">        <span class="attr">type:</span> <span class="string">com.zaxxer.hikari.HikariDataSource</span> <span class="comment"># 使用 Hikari 数据库连接池</span></span><br><span class="line">        <span class="attr">driver-class-name:</span> <span class="string">com.mysql.cj.jdbc.Driver</span></span><br><span class="line">        <span class="attr">jdbc-url:</span> <span class="string">jdbc:mysql://127.0.0.1:3306/labs-springboot2-multiple?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai</span></span><br><span class="line">        <span class="attr">username:</span> <span class="string">root</span></span><br><span class="line">        <span class="attr">password:</span> <span class="string">root</span></span><br><span class="line">      <span class="comment"># 订单 orders 从库数据源配置</span></span><br><span class="line">      <span class="attr">ds-slave-2:</span></span><br><span class="line">        <span class="attr">type:</span> <span class="string">com.zaxxer.hikari.HikariDataSource</span> <span class="comment"># 使用 Hikari 数据库连接池</span></span><br><span class="line">        <span class="attr">driver-class-name:</span> <span class="string">com.mysql.cj.jdbc.Driver</span></span><br><span class="line">        <span class="attr">jdbc-url:</span> <span class="string">jdbc:mysql://127.0.0.1:3306/mysql_learn?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai</span></span><br><span class="line">        <span class="attr">username:</span> <span class="string">root</span></span><br><span class="line">        <span class="attr">password:</span> <span class="string">root</span></span><br><span class="line">    <span class="comment"># 读写分离配置，对应 YamlMasterSlaveRuleConfiguration 配置类</span></span><br><span class="line">    <span class="attr">masterslave:</span></span><br><span class="line">      <span class="attr">name:</span> <span class="string">ms</span> <span class="comment"># 名字，任意，需要保证唯一</span></span><br><span class="line">      <span class="attr">master-data-source-name:</span> <span class="string">ds-master</span> <span class="comment"># 主库数据源</span></span><br><span class="line">      <span class="attr">slave-data-source-names:</span> <span class="string">ds-slave-1,</span> <span class="string">ds-slave-2</span> <span class="comment"># 从库数据源</span></span><br><span class="line">    <span class="comment"># 拓展属性配置</span></span><br><span class="line">    <span class="attr">props:</span></span><br><span class="line">      <span class="attr">sql:</span></span><br><span class="line">        <span class="attr">show:</span> <span class="literal">true</span> <span class="comment"># 打印 SQL</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># mybatis-plus 配置内容</span></span><br><span class="line"><span class="attr">mybatis-plus:</span></span><br><span class="line">  <span class="attr">configuration:</span></span><br><span class="line">    <span class="attr">map-underscore-to-camel-case:</span> <span class="literal">true</span> <span class="comment"># 虽然默认为 true ，但是还是显示去指定下。</span></span><br><span class="line">  <span class="attr">global-config:</span></span><br><span class="line">    <span class="attr">db-config:</span></span><br><span class="line">      <span class="attr">id-type:</span> <span class="string">none</span> <span class="comment"># 虽然 MyBatis Plus 也提供 ID 生成策略，但是还是使用 Sharding-JDBC 的</span></span><br><span class="line">      <span class="attr">logic-delete-value:</span> <span class="number">1</span> <span class="comment"># 逻辑已删除值(默认为 1)</span></span><br><span class="line">      <span class="attr">logic-not-delete-value:</span> <span class="number">0</span> <span class="comment"># 逻辑未删除值(默认为 0)</span></span><br><span class="line">  <span class="attr">mapper-locations:</span> <span class="string">classpath*:mapper/*.xml</span></span><br><span class="line">  <span class="attr">type-aliases-package:</span> <span class="string">cn.javadog.springboot2.datasource.sharding.baomidou.dataobject</span></span><br></pre></td></tr></table></figure><p><strong>Sharding-JDBC 配置项</strong></p><ul><li><code>spring.shardingsphere.datasource</code> 配置项下，我们配置了 一个主数据源 <code>ds-master</code> 、两个从数据源 <code>ds-slave-1</code>、<code>ds-slave-2</code> 。</li><li><code>spring.shardingsphere.masterslave</code> 配置项下，配置了读写分离。对于从库来说，Sharding-JDBC 提供了多种负载均衡策略，默认为轮询。</li><li>因为我本地并未搭建 MySQL 一主多从的环境，所以是通过创建了 <code>labs-springboot2-multiple</code>、<code>mysql_learn</code> 库，手动模拟作为 <code>test_orders</code> 的从库。</li></ul><p><strong>MyBatis-Plus 配合项</strong></p><ul><li><code>mybatis-plus</code> 增加了更多配置项，也因此我们无需在配置 <code>mybatis-config.xml</code>配置文件。</li><li>更多的 MyBatis-Plus 配置项，可以看看 <a href="https://mybatis.plus/config/#mapperlocations" target="_blank" rel="noopener">MyBatis-Plus 使用配置</a> 。</li></ul><h2 id="2-4-OrderDO"><a href="#2-4-OrderDO" class="headerlink" title="2.4 OrderDO"></a>2.4 OrderDO</h2><p>在 <code>cn.javadog.springboot2.datasource.sharding.baomidou.dataobject</code> 包路径下，创建 <code>OrderDO.java</code> 类，订单 DO 。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderDO.java</span></span><br><span class="line"></span><br><span class="line"><span class="meta">@Data</span></span><br><span class="line"><span class="meta">@Accessors</span>(chain = <span class="keyword">true</span>)</span><br><span class="line"><span class="meta">@TableName</span>(value = <span class="string">"dynamic_datasource_orders"</span>)</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">OrderDO</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 订单编号</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="keyword">private</span> Long id;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 用户编号</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="keyword">private</span> Integer userId;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><ul><li>增加了 <a href="https://mybatis.plus/guide/annotation.html#tablename" target="_blank" rel="noopener">@TableName</a> 注解，设置了 OrderDO 对应的表名是 <code>dynamic_datasource_orders</code> 。毕竟，我们要使用 MyBatis-Plus 给咱自动生成 CRUD 操作。</li></ul><p>对应的创建表的 SQL 如下：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">-- ----------------------------</span><br><span class="line">-- Table structure for orders</span><br><span class="line">-- ----------------------------</span><br><span class="line">DROP TABLE IF EXISTS &#96;dynamic_datasource_orders&#96;;</span><br><span class="line">CREATE TABLE &#96;dynamic_datasource_orders&#96; (</span><br><span class="line">  &#96;id&#96; int(11) NOT NULL AUTO_INCREMENT COMMENT &#39;订单编号&#39;,</span><br><span class="line">  &#96;user_id&#96; int(16) DEFAULT NULL COMMENT &#39;用户编号&#39;,</span><br><span class="line">  PRIMARY KEY (&#96;id&#96;)</span><br><span class="line">) ENGINE&#x3D;InnoDB AUTO_INCREMENT&#x3D;11 DEFAULT CHARSET&#x3D;utf8mb4 COLLATE&#x3D;utf8mb4_bin COMMENT&#x3D;&#39;订单表&#39;;</span><br></pre></td></tr></table></figure><h2 id="2-5-OrderMapper"><a href="#2-5-OrderMapper" class="headerlink" title="2.5 OrderMapper"></a>2.5 OrderMapper</h2><p>在 <code>cn.javadog.springboot2.datasource.sharding.baomidou.mapper</code> 包路径下，创建 <code>OrderMapper</code> 接口。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderMapper.java</span></span><br><span class="line"></span><br><span class="line"><span class="meta">@Repository</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">interface</span> <span class="title">OrderMapper</span> <span class="keyword">extends</span> <span class="title">BaseMapper</span>&lt;<span class="title">OrderDO</span>&gt; </span>&#123;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><ul><li>继承了 <code>com.baomidou.mybatisplus.core.mapper.BaseMapper</code> 接口，这样常规的 CRUD 操作，MyBatis-Plus 就可以替我们自动生成。一般来说，开发 CRUD 业务的时候，最枯燥的就是要写 CRUD 的常用 SQL ，完全跟不上卡姆的思绪哈。</li></ul><h2 id="2-6-简单测试"><a href="#2-6-简单测试" class="headerlink" title="2.6 简单测试"></a>2.6 简单测试</h2><p>创建 <code>OrderMapperTest</code> 测试类，我们来测试一下简单的 OrderMapper 的读写操作。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderMapperTest.java</span></span><br><span class="line"></span><br><span class="line"><span class="meta">@RunWith</span>(SpringRunner<span class="class">.<span class="keyword">class</span>)</span></span><br><span class="line"><span class="class">@<span class="title">SpringBootTest</span>(<span class="title">classes</span> </span>= Application<span class="class">.<span class="keyword">class</span>)</span></span><br><span class="line"><span class="class"><span class="title">public</span> <span class="title">class</span> <span class="title">OrderMapperTest</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Autowired</span></span><br><span class="line">    <span class="keyword">private</span> OrderMapper orderMapper;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 测试从库的负载均衡</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="meta">@Test</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">testSelectById</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">for</span> (<span class="keyword">int</span> i = <span class="number">0</span>; i &lt; <span class="number">2</span>; i++) &#123;</span><br><span class="line">            OrderDO order = orderMapper.selectById(<span class="number">1</span>);</span><br><span class="line">            System.out.println(order);</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 测试强制访问主库</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="meta">@Test</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">testSelectById02</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        <span class="keyword">try</span> (HintManager hintManager = HintManager.getInstance()) &#123;</span><br><span class="line">            <span class="comment">// 设置强制访问主库</span></span><br><span class="line">            hintManager.setMasterRouteOnly();</span><br><span class="line">            <span class="comment">// 执行查询</span></span><br><span class="line">            OrderDO order = orderMapper.selectById(<span class="number">1</span>);</span><br><span class="line">            System.out.println(order);</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 插入</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="meta">@Test</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">testInsert</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        OrderDO order = <span class="keyword">new</span> OrderDO();</span><br><span class="line">        order.setUserId(<span class="number">10</span>);</span><br><span class="line">        orderMapper.insert(order);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><p><strong>① <code>#testSelectById()</code> 测试方法</strong></p><p>执行日志如下：</p><figure class="highlight verilog"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// 第 1 次查询</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">47</span>:<span class="number">10</span><span class="variable">.272</span>  INFO <span class="number">11567</span> --- [           main] ShardingSphere-SQL                       : Rule Type: master-slave</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">47</span>:<span class="number">10</span><span class="variable">.272</span>  INFO <span class="number">11567</span> --- [           main] ShardingSphere-SQL                       : SQL: SELECT id,user_id FROM dynamic_datasource_orders WHERE id=?  ::: DataSources: ds-slave-<span class="number">1</span></span><br><span class="line"></span><br><span class="line"><span class="comment">// 第 2 次查询</span></span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">47</span>:<span class="number">10</span><span class="variable">.322</span>  INFO <span class="number">11567</span> --- [           main] ShardingSphere-SQL                       : Rule Type: master-slave</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">47</span>:<span class="number">10</span><span class="variable">.322</span>  INFO <span class="number">11567</span> --- [           main] ShardingSphere-SQL                       : SQL: SELECT id,user_id FROM dynamic_datasource_orders WHERE id=?  ::: DataSources: ds-slave-<span class="number">2</span></span><br></pre></td></tr></table></figure><ul><li>默认情况下，Sharding-JDBC 使用 <a href="https://shardingsphere.apache.org/document/current/cn/features/read-write-split/" target="_blank" rel="noopener">读写分离</a> 功能时，读取从库。</li><li>并且，支持从库的负载均衡，默认采用<strong>轮询</strong>的算法。所以，我们可以看到第 1 次查询 <code>ds-slave-1</code> 数据源，第 2 次查询 <code>ds-slave-2</code> 数据源。</li></ul><p><strong>② <code>#testSelectById02()</code> 测试方法</strong></p><p>执行日志如下：</p><figure class="highlight verilog"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">48</span>:<span class="number">33</span><span class="variable">.566</span>  INFO <span class="number">11570</span> --- [           main] ShardingSphere-SQL                       : Rule Type: master-slave</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">48</span>:<span class="number">33</span><span class="variable">.566</span>  INFO <span class="number">11570</span> --- [           main] ShardingSphere-SQL                       : SQL: SELECT id,user_id FROM dynamic_datasource_orders WHERE id=?  ::: DataSources: ds-master</span><br></pre></td></tr></table></figure><ul><li><p>测试强制访问主库。在一些业务场景下，对数据延迟敏感，所以只能强制读取主库。此时，可以使用</p><p><code>HintManager</code> 强制访问主库。</p><ul><li>不过要注意，在使用完后，需要去清理下 HintManager （HintManager 是基于线程变量，透传给 Sharding-JDBC 的内部实现），避免污染下次请求，一直强制访问主库。</li><li>Sharding-JDBC 比较贴心，HintManager 实现了 AutoCloseable 接口，可以通过 <a href="https://www.cnblogs.com/barrywxx/p/9993005.html" target="_blank" rel="noopener">Try-with-resources</a> 机制，自动关闭。</li></ul></li></ul><p><strong>③ <code>#testInsert()</code> 测试方法</strong></p><figure class="highlight verilog"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">49</span>:<span class="number">39</span><span class="variable">.319</span>  INFO <span class="number">11576</span> --- [           main] ShardingSphere-SQL                       : Rule Type: master-slave</span><br><span class="line"><span class="number">2020</span>-<span class="number">02</span>-<span class="number">17</span> <span class="number">17</span>:<span class="number">49</span>:<span class="number">39</span><span class="variable">.320</span>  INFO <span class="number">11576</span> --- [           main] ShardingSphere-SQL                       : SQL: INSERT INTO dynamic_datasource_orders  ( id,</span><br><span class="line">user_id )  VALUES  ( ?,</span><br><span class="line">? ) ::: DataSources: ds-master</span><br></pre></td></tr></table></figure><ul><li>写入操作时，直接访问主库 <code>ds-master</code> 数据源。</li></ul><h2 id="2-7-详细测试"><a href="#2-7-详细测试" class="headerlink" title="2.7 详细测试"></a>2.7 详细测试</h2><p>在 <code>cn.javadog.springboot2.datasource.sharding.baomidou.service</code>包路径下，创建 <code>OrderService.java</code>类。代码如下：</p><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// OrderService.java</span></span><br><span class="line"></span><br><span class="line"><span class="meta">@Service</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">OrderService</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Autowired</span></span><br><span class="line">    <span class="keyword">private</span> OrderMapper orderMapper;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Transactional</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">add</span><span class="params">(OrderDO order)</span> </span>&#123;</span><br><span class="line">        <span class="comment">// &lt;1.1&gt; 这里先假模假样的读取一下。读取从库</span></span><br><span class="line">        OrderDO exists = orderMapper.selectById(<span class="number">1</span>);</span><br><span class="line">        System.out.println(exists);</span><br><span class="line"></span><br><span class="line">        <span class="comment">// &lt;1.2&gt; 插入订单</span></span><br><span class="line">        orderMapper.insert(order);</span><br><span class="line"></span><br><span class="line">        <span class="comment">// &lt;1.3&gt; 这里先假模假样的读取一下。读取主库</span></span><br><span class="line">        exists = orderMapper.selectById(<span class="number">1</span>);</span><br><span class="line">        System.out.println(exists);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> OrderDO <span class="title">findById</span><span class="params">(Integer id)</span> </span>&#123;</span><br><span class="line">        <span class="keyword">return</span> orderMapper.selectById(id);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure><ul><li>我们创建了 <code>OrderServiceTest</code> 测试类，可以测试上面编写的两个方法。</li><li>在 <code>#add(OrderDO order)</code> 方法中，开启事务，插入一条订单记录。<ul><li><code>&lt;1.1&gt;</code> 处，往<strong>从库</strong>发起一次订单查询。在 Sharding-JDBC 的读写分离策略里，默认读取从库。</li><li><code>&lt;1.2&gt;</code> 处，往<strong>主库</strong>发起一次订单写入。写入，肯定是操作主库的。</li><li><code>&lt;1.3&gt;</code> 处，往<strong>主库</strong>发起一次订单查询。在 Sharding-JDBC 中，读写分离约定：<strong>同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性。</strong></li></ul></li><li>在 <code>#findById(Integer id)</code> 方法，往<strong>从库</strong>发起一次订单查询。</li></ul><hr><p>实际场景下，我们会是<strong>分库分表 + 读写分离</strong>共同使用，所以胖友可以参考 <a href="https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/configuration/" target="_blank" rel="noopener">《ShardingSphere &gt; 用户手册 &gt; Sharding-JDBC &gt; 配置手册》</a> 文档，尝试自己实现一个这样的示例。</p><p>因为文档提供的是 Properties 的格式，如果胖友想转换成 YAML 格式，可以使用 <a href="https://www.toyaml.com/index.html" target="_blank" rel="noopener">ToYaml.com</a> 工具。</p><h1 id="666-彩蛋"><a href="#666-彩蛋" class="headerlink" title="666. 彩蛋"></a>666. 彩蛋</h1><p>在 Apache ShardingSphere 中，目前提供了 Sharding-JDBC 和 Sharding-Proxy 两种方式，未来会有 Sharding-Sidecar 方式。那么，怎么做选择呢？</p><blockquote><p>Sharding-JDBC 采用无中心化架构，适用于 Java 开发的高性能的轻量级 OLTP 应用。</p><p>Sharding-Proxy 提供静态入口以及异构语言的支持，适用于 OLAP 应用以及对分片数据库进行管理和运维的场景。</p></blockquote><p>Sharding-JDBC ，相比 Sharding-Proxy 来说，是基于 client 模式，无需经过 proxy 一层的性能损耗，也不用考虑 proxy 的高可用，所以对于 Java 项目来说，更加被推荐。目前，阿里、京东、美团等公司，都采用 client 模式的分库分表中间件。</p><p>当然，Sharding-Proxy 也是有其使用的场景。我们可以搭建一个 Sharding-Proxy 服务，然后使用 Navicat 等 MySQL GUI 工具连接该服务，方便查询数据。</p><p>另外，因为本文是在使用 Spring Boot 的情况下，分库分表的入门文章，所以 ShardingSphere 提供的其它功能并未去编写，胖友可以自己尝试下。</p><ul><li><a href="https://shardingsphere.apache.org/document/current/cn/manual/sharding-proxy/" target="_blank" rel="noopener">《Sharding-Proxy》</a></li><li><a href="https://shardingsphere.apache.org/document/current/cn/manual/sharding-ui/" target="_blank" rel="noopener">《Sharding-UI》</a></li><li><a href="https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/usage/orchestration/" target="_blank" rel="noopener">《编排治理》</a></li><li><a href="https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/usage/transaction/" target="_blank" rel="noopener">《分布式事务》</a></li><li><a href="https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/usage/encrypt/" target="_blank" rel="noopener">《数据脱敏》</a></li></ul><p>推荐阅读：</p><ul><li><a href="https://calm_java.gitee.io/blog/2020/02/13/labs-springboot2-dynamic-datasource/">《Spring Boot 多数据源（读写分离）入门》</a> 对应 <a href="https://github.com/yuyong725/labs-springboot2/tree/master/labs-springboot2-data-access/labs-springboot2-datasource-dynamic" target="_blank" rel="noopener">labs-springboot2-datasource-dynamic</a> 。</li></ul></div><div class="article-footer"><blockquote class="mt-2x"><ul class="post-copyright list-unstyled"><li class="post-copyright-link hidden-xs"><strong>本文链接：</strong> <a href="https://calm_java.gitee.io/blog/2020/02/17/labs-springboot2-sharding-datasource/" title="Spring Boot 分库分表入门" target="_blank" rel="external">https://calm_java.gitee.io/blog/2020/02/17/labs-springboot2-sharding-datasource/</a></li><li class="post-copyright-license"><strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="http://creativecommons.org/licenses/by/4.0/deed.zh" target="_blank" rel="external">CC BY 4.0 CN协议</a> 许可协议。转载请注明出处！</li></ul></blockquote><div class="panel panel-default panel-badger"><div class="panel-body"><figure class="media"><div class="media-left"><a href="https://github.com/yuyong725" target="_blank" class="img-burn thumb-sm visible-lg"><img src="/blog/images/avatar.jpg" class="img-rounded w-full" alt=""></a></div><div class="media-body"><h3 class="media-heading"><a href="https://github.com/yuyong725" target="_blank"><span class="text-dark">卡姆</span><small class="ml-1x">a Java coder need calm</small></a></h3><div>享受编程</div></div></figure></div></div></div></article><section id="comments"></section></div><nav class="bar bar-footer clearfix" data-stick-bottom><div class="bar-inner"><ul class="pager pull-left"><li class="prev"><a href="/blog/2020/02/17/labs-springboot2-database-version-control/" title="Spring Boot 数据库版本管理入门"><i class="icon icon-angle-left" aria-hidden="true"></i><span>&nbsp;&nbsp;上一篇</span></a></li><li class="next"><a href="/blog/2020/02/13/labs-springboot2-dynamic-datasource/" title="Spring Boot 多数据源（读写分离）入门"><span>下一篇&nbsp;&nbsp;</span><i class="icon icon-angle-right" aria-hidden="true"></i></a></li><li class="toggle-toc"><a class="toggle-btn" data-toggle="collapse" href="#collapseToc" aria-expanded="true" title="文章目录" role="button"><span>[&nbsp;</span><span>文章目录</span> <i class="text-collapsed icon icon-anchor"></i> <i class="text-in icon icon-close"></i> <span>]</span></a></li></ul><button type="button" class="btn btn-fancy btn-donate pop-onhover bg-gradient-warning" data-toggle="modal" data-target="#donateModal"><span>赏</span></button><div class="bar-right"><div class="share-component" data-sites="wechat" data-mobile-sites="wechat"></div></div></div></nav><div class="modal modal-center modal-small modal-xs-full fade" id="donateModal" tabindex="-1" role="dialog"><div class="modal-dialog" role="document"><div class="modal-content donate"><button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button><div class="modal-body"><div class="donate-box"><div class="donate-head"><p>感谢您的支持，我会继续努力的!</p></div><div class="tab-content"><div role="tabpanel" class="tab-pane fade active in" id="alipay"><div class="donate-payimg"><img src="/blog/images/donate/alipayimg.jpeg" alt="扫码支持" title="扫一扫"></div><p class="text-muted mv">扫码打赏，你说多少就多少</p><p class="text-grey">打开支付宝扫一扫，即可进行扫码打赏哦</p></div><div role="tabpanel" class="tab-pane fade" id="wechatpay"><div class="donate-payimg"><img src="/blog/images/donate/wechatpayimg.jpeg" alt="扫码支持" title="扫一扫"></div><p class="text-muted mv">扫码打赏，你说多少就多少</p><p class="text-grey">打开微信扫一扫，即可进行扫码打赏哦</p></div></div><div class="donate-footer"><ul class="nav nav-tabs nav-justified" role="tablist"><li role="presentation" class="active"><a href="#alipay" id="alipay-tab" role="tab" data-toggle="tab" aria-controls="alipay" aria-expanded="true"><i class="icon icon-alipay"></i> 支付宝</a></li><li role="presentation"><a href="#wechatpay" role="tab" id="wechatpay-tab" data-toggle="tab" aria-controls="wechatpay" aria-expanded="false"><i class="icon icon-wepay"></i> 微信支付</a></li></ul></div></div></div></div></div></div></main><footer class="footer" itemscope itemtype="http://schema.org/WPFooter"></footer><script src="//cdn.jsdelivr.net/npm/jquery@1.12.4/dist/jquery.min.js"></script><script>window.jQuery||document.write('<script src="js/jquery.min.js"><\/script>')</script><script src="/blog/js/plugin.min.js"></script><script src="/blog/js/application.js"></script><script>!function(T){var N={TRANSLATION:{POSTS:"文章",PAGES:"页面",CATEGORIES:"分类",TAGS:"标签",UNTITLED:"(未命名)"},ROOT_URL:"/blog/",CONTENT_URL:"/blog/content.json"};T.INSIGHT_CONFIG=N}(window)</script><script src="/blog/js/insight.js"></script><script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><script src="//cdn.jsdelivr.net/npm/gitalk@1.4.0/dist/gitalk.min.js"></script><script src="//cdn.jsdelivr.net/npm/blueimp-md5@2.10.0/js/md5.min.js"></script><script type="text/javascript">var gitalk=new Gitalk({clientID:"adf0c8bfdd8796029917",clientSecret:"a88c2e2e83e2b180f62ad927dcb347871dfe9f4b",repo:"myblog-comment",owner:"yuyong725",admin:["yuyong725"],id:md5(location.pathname),distractionFreeMode:!0});gitalk.render("comments")</script></body></html><!-- rebuild by neat -->